package com.han.jdbc.mysql.statement;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.junit.Test;

import com.han.jdbc.mysql.uils.JDBCUtils;

/**
 * @author hanpeng
 * @version 创建时间：2020年7月5日 下午9:36:44
 * @ClassName 类名称
 * @Description 类描述
 */
public class PreparedStatementBatchInsertTest {
	@Test
	public void testInsert1() {
		Connection conn = null;
		PreparedStatement ps = null;
		try {

			long start = System.currentTimeMillis();
			conn = JDBCUtils.getConnection();
			String sql = "insert into goods(name)values(?)";

			ps = conn.prepareStatement(sql);
			for (int i = 1; i <= 20000; i++) {
				ps.setObject(1, "name_" + i);

				ps.execute();
			}

			long end = System.currentTimeMillis();

			System.out.println("花费的时间为：" + (end - start));// 20000:83065
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, ps);

		}
	}
	//批量操作的api addBatch()、executeBatch()、clearBatch()
	
	@Test
	public void testInsert2() {
		Connection conn = null;
		PreparedStatement ps = null;
		try {

			long start = System.currentTimeMillis();

			conn = JDBCUtils.getConnection();
			String sql = "insert into goods(name)values(?)";
			ps = conn.prepareStatement(sql);
			for (int i = 1; i <= 1000000; i++) {
				ps.setObject(1, "name_" + i);

				// 1."攒"sql
				ps.addBatch();

				if (i % 500 == 0) {
					// 2.执行batch
					ps.executeBatch();

					// 3.清空batch
					ps.clearBatch();
				}else if (i==1000000&&i % 500!=0) {
					ps.executeBatch();
				}

			}

			long end = System.currentTimeMillis();

			System.out.println("花费的时间为：" + (end - start));// 20000:83065 -- 565
		} catch (Exception e) { // 1000000:16086
			e.printStackTrace();
			
		} finally {
			JDBCUtils.closeResource(conn, ps);

		}

	}
	//批量插入的方式四：设置连接不允许自动提交数据
		@Test
		public void testInsert3() {
			Connection conn = null;
			PreparedStatement ps = null;
			try {
				
				long start = System.currentTimeMillis();
				
				conn = JDBCUtils.getConnection();
				
				//设置不允许自动提交数据
				conn.setAutoCommit(false);
				
				String sql = "insert into goods(name)values(?)";
				ps = conn.prepareStatement(sql);
				for(int i = 1;i <= 1000000;i++){
					ps.setObject(1, "name_" + i);
					
					//1."攒"sql
					ps.addBatch();
					
					if(i % 500 == 0){
						//2.执行batch
						ps.executeBatch();
						
						//3.清空batch
						ps.clearBatch();
					}else if (i==1000000&&i % 500 != 0) {
						ps.executeBatch();
					}
					
				}
				
				//提交数据
				conn.commit();
				
				long end = System.currentTimeMillis();
				
				System.out.println("花费的时间为：" + (end - start));//20000:83065 -- 565
			} catch (Exception e) {								//1000000:16086 -- 5114
				e.printStackTrace();
			}finally{
				try {
					conn.rollback(); 
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				JDBCUtils.closeResource(conn, ps);
				
			}
			
		}
}
